﻿using System.Text;

namespace Maltose.Data
{
    /// <summary>
    /// SQL分页生成
    /// </summary>
    public class SqlPageBuilder
    {

        /// <summary>
        /// 创建自定义T-SQL的分页
        /// </summary>
        /// <param name="p">参数</param>
        /// <returns></returns>
        public static string CustomSQL(SqlPageCustom p)
        {
            var sql = new StringBuilder();

            /*  这一段代码是兼容  MSSQL 2020 以下的分页
            int start = (p.PageIndex - 1) * p.PageSize + 1;
            int end = p.PageIndex * p.PageSize;
            sql.Append("SELECT * FROM (");
            sql.Append($"SELECT ROW_NUMBER() OVER(ORDER BY {p.KeyFiled}) AS ROW_NUMBER,");
            sql.Append($"{p.Sql} ) AS Tab ");
            sql.Append($" WHERE ROW_NUMBER BETWEEN {start} AND {end};");
            */

            int start = (p.PageIndex - 1) * p.PageSize;
            int end = p.PageIndex * p.PageSize;

            sql.Append($"{p.Sql} ORDER BY {p.KeyFiled} OFFSET {start} ROWS FETCH NEXT {end} ROWS ONLY;");
            sql.Append($"SELECT COUNT(0) AS DataCount FROM (SELECT {p.Sql}) AS CTB;");

            return sql.ToString();
        }

        /// <summary>
        /// 创建自定义T-SQL的分页 不会返回统计数据总和
        /// </summary>
        /// <param name="p">参数</param>
        /// <returns></returns>
        public static string CustomSQLNoneCount(SqlPageCustomNoneCount p)
        {
            int start = (p.PageIndex - 1) * p.PageSize;
            int end = p.PageIndex * p.PageSize;
            var sql = new StringBuilder();
            sql.Append($"{p.Sql} ORDER BY {p.KeyFiled} OFFSET {start} ROWS FETCH NEXT {end} ROWS ONLY;");
            return sql.ToString();
        }

        /// <summary>
        /// 创建自定义正常分页数据
        /// </summary>
        /// <param name="p">参数</param>
        /// <returns></returns>
        public static string NormalSQL(SqlPageNormal p)
        {

            var sql = new StringBuilder();
            int start = (p.PageIndex - 1) * p.PageSize;
            int end = p.PageIndex * p.PageSize;

            /* 这一段代码是兼容  MSSQL 2020 以下的分页
             sql.Append("SELECT * FROM (");
             sql.Append($"SELECT ROW_NUMBER() OVER(ORDER BY {p.KeyFiled}) AS ROW_NUMBER,");
             sql.Append($"{p.ShowField} FROM dbo.[{p.TableName}] {winthLock} WHERE {p.Where} ) AS Tab ");
             sql.Append($"WHERE ROW_NUMBER BETWEEN {start} AND {end};");
             */

            if (!string.IsNullOrWhiteSpace(p.OrderBy))
            {
                p.OrderBy = $" ORDER BY {p.OrderBy}";
                sql.Append($" SELECT {p.ShowField} FROM ( SELECT {p.ShowField} FROM {p.TableName} {p.OrderBy} ) FROM TB ");
            }
            else
            {
                sql.Append($" SELECT {p.ShowField} FROM {p.TableName}");
            }
            sql.Append($" ORDER BY {p.KeyFiled} OFFSET {start} ROWS FETCH NEXT {end} ROWS ONLY;");

            sql.Append($" SELECT COUNT(0) AS DataCount FROM (SELECT {p.TableName}) AS CTB;");
            return sql.ToString();
        }

        /// <summary>
        /// 正常分页没有统计数 只有返回的数据
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public static string NormalSQLNoneCount(SqlPageNormalNoneCount p)
        {
            int start = (p.PageIndex - 1) * p.PageSize;
            int end = p.PageIndex * p.PageSize;
            var sql = new StringBuilder();
            if (!string.IsNullOrWhiteSpace(p.OrderBy))
            {
                p.OrderBy = $" ORDER BY {p.OrderBy}";
                sql.Append($" SELECT {p.ShowField} FROM ( SELECT {p.ShowField} FROM {p.TableName} {p.OrderBy} ) FROM TB ");
            }
            else
            {
                sql.Append($" SELECT {p.ShowField} FROM {p.TableName}");
            }
            sql.Append($" ORDER BY {p.KeyFiled} OFFSET {start} ROWS FETCH NEXT {end} ROWS ONLY;");
            return sql.ToString();
        }
    }
}
